﻿using System;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading;
using Loki.Data;
using Loki.Utils;

namespace Luna.Data.SqlCe
{
    public abstract class SqlCeDatabaseAdapter : LokiObject, IDatabaseAdapter
    {
        public string ConnectionString
        {
            get;
            set;
        }

        public string CurrentUser
        {
            get
            {
                return Environment.UserName;
            }
        }

        public string FileName
        {
            get
            {
                if (!string.IsNullOrEmpty(ConnectionString))
                {
                    SqlCeConnectionStringBuilder L_Builder = new SqlCeConnectionStringBuilder(ConnectionString);
                    return L_Builder.DataSource;
                }
                else
                {
                    return string.Empty;
                }
            }
        }

        public abstract bool Initialized
        {
            get;
        }

        public IDatabaseCommand CreateCommand(string P_Name)
        {
            return new SqlCeDatabaseCommand(P_Name);
        }

        public IDatabaseCommand CreateCommand(string P_Name, CommandType P_Type)
        {
            return new SqlCeDatabaseCommand(P_Name);
        }

        public virtual void Initialize(NameValueCollection P_Params)
        {
        }

        protected abstract string GetRequest(string P_CommandName);

        #region IDatabaseAdapter Members

        private const string FILE_SOURCE_REGEX = "Data Source=(.*?);";
        private const string SELECT_ALL = "SELECT * FROM {0}";
        private string _DataSouceDescription = string.Empty;

        public string DataSourceDescription
        {
            get
            {
                if (_DataSouceDescription == string.Empty)
                {
                    Regex L_FileSource = new Regex(FILE_SOURCE_REGEX, RegexOptions.IgnoreCase);
                    var L_Match = L_FileSource.Match(ConnectionString);
                    if (L_Match.Success)
                    {
                        string L_DataSouceDescription = L_Match.Groups[1].Value;
                        Interlocked.CompareExchange(ref _DataSouceDescription, L_DataSouceDescription, string.Empty);
                    }
                }

                return _DataSouceDescription;
            }
        }

        /// <summary>
        /// Bulks insert data in a table of the same name.
        /// </summary>
        /// <param name="P_Table">The table.</param>
        public int BulkInsert(DataTable P_Table)
        {
            SqlCeDataAdapter L_Adapter = new SqlCeDataAdapter(string.Format(SELECT_ALL, P_Table), ConnectionString);

            SqlCeCommandBuilder L_Builder = new SqlCeCommandBuilder(L_Adapter);
            L_Builder.GetInsertCommand();
            return L_Adapter.Update(P_Table);
        }

        public virtual void ExecuteBatch(string P_BatchCommands)
        {
            using (var connection = new SqlCeConnection(ConnectionString))
            using (var command = new SqlCeCommand())
            {
                connection.Open();
                command.Connection = connection;
                command.CommandType = CommandType.Text;

                string[] queries = P_BatchCommands.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
                foreach (var query in queries)
                {
                    command.CommandText = query;
                    command.ExecuteNonQuery();
                }

                connection.Close();
            }
        }

        /// <summary>
        /// Executes command.
        /// </summary>
        /// <param name="P_Command">The command.</param>
        /// <param name="P_Transaction">The transaction.</param>
        public virtual void ExecuteNonQuery(IDatabaseCommand P_Command, IDbTransaction P_Transaction)
        {
            using (SqlCeCommand L_Command = new SqlCeCommand())
            {
                L_Command.CommandText = GetRequest(P_Command.Name);
                L_Command.Transaction = P_Transaction as SqlCeTransaction;
                L_Command.Connection = L_Command.Transaction.Connection as SqlCeConnection;
                L_Command.Parameters.AddRange(P_Command.Parameters.ToArray());
                L_Command.CommandType = P_Command.Type;

                L_Command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Executes the query.
        /// </summary>
        /// <param name="P_Command">The command.</param>
        /// <param name="P_Transaction">The transaction.</param>
        public virtual IDataReader ExecuteQuery(IDatabaseCommand P_Command, IDbTransaction P_Transaction)
        {
            using (SqlCeCommand L_Command = new SqlCeCommand())
            {
                L_Command.CommandText = GetRequest(P_Command.Name);
                L_Command.Transaction = P_Transaction as SqlCeTransaction;
                L_Command.Connection = L_Command.Transaction.Connection as SqlCeConnection;
                L_Command.Parameters.AddRange(P_Command.Parameters.ToArray());
                L_Command.CommandType = P_Command.Type;

                return L_Command.ExecuteReader();
            }
        }

        /// <summary>
        /// Executes the query and return an unique value.
        /// </summary>
        /// <param name="P_Command">The command.</param>
        /// <param name="P_Transaction">The transaction.</param>
        public virtual object ExecuteScalar(IDatabaseCommand P_Command, IDbTransaction P_Transaction)
        {
            using (SqlCeCommand L_Command = new SqlCeCommand())
            {
                L_Command.CommandText = GetRequest(P_Command.Name);
                L_Command.Transaction = P_Transaction as SqlCeTransaction;
                L_Command.Connection = L_Command.Transaction.Connection as SqlCeConnection;
                L_Command.Parameters.AddRange(P_Command.Parameters.ToArray());
                L_Command.CommandType = P_Command.Type;

                return L_Command.ExecuteScalar();
            }
        }

        #endregion IDatabaseAdapter Members

        #region Transactions

        /// <summary>
        /// Commits the transaction.
        /// </summary>
        /// <param name="P_Tran">Transaction to commit.</param>
        public bool CommitTransaction(IDbTransaction P_Tran)
        {
            SqlCeConnection L_Conn = P_Tran.Connection as SqlCeConnection;
            P_Tran.Commit();
            if (L_Conn != null)
            {
                L_Conn.Dispose();
            }

            return true;
        }

        /// <summary>
        /// Open transaction on current database (new connection opened).
        /// </summary>
        /// <returns>New transaction.</returns>
        public IDbTransaction OpenTransaction()
        {
            return OpenTransaction(IsolationLevel.ReadCommitted);
        }

        public IDbTransaction OpenTransaction(IsolationLevel P_Level)
        {
            SqlCeConnection L_Conn = new SqlCeConnection(ConnectionString);
            L_Conn.Open();
            return L_Conn.BeginTransaction(P_Level);
        }

        /// <summary>
        /// Rollbacks the transaction.
        /// </summary>
        /// <param name="P_Tran">Transaction to rollback.</param>
        public bool RollbackTransaction(IDbTransaction P_Tran)
        {
            if (P_Tran != null)
            {
                SqlCeConnection L_Conn = P_Tran.Connection as SqlCeConnection;
                P_Tran.Rollback();
                if (L_Conn != null)
                {
                    L_Conn.Dispose();
                }

                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion Transactions
    }
}